﻿Imports System.Data.SqlClient
Public Class frmEditProspect
    Public prospect As Integer
    Private Sub BindProspect(ByVal id As Integer)
        prospect = id
        Try
            Dim cmd As New SqlCommand
            Dim reader As SqlDataReader
            cmd.Connection = cnn
            cnn.Open()
            cmd.CommandText = "select * from Prospects where SNo=" & id
            reader = cmd.ExecuteReader
            While reader.Read
                txtCompanyName.Text = reader.Item("Name")
            End While
            reader.Close()
            cmd.CommandText = "select * from ProspectContacts where ProsID=" & id
            reader = cmd.ExecuteReader
            While reader.Read
                GridContactPerson.Rows.Add(reader.Item("ContactName"), reader.Item("Tele"), reader.Item("Email"), reader.Item("Position"))
            End While
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
        End Try
    End Sub
    Private Sub clear()

    End Sub
    Public Sub save()
        Try
            Dim cmd As New SqlCommand
            Dim trans As SqlTransaction
            cnn.Open()
            cmd.Connection = cnn
            trans = cnn.BeginTransaction
            cmd.Transaction = trans

            cmd.CommandText = "update Prospects set Name=@Name where SNo=@SNo"
            cmd.Parameters.AddWithValue("@Name", txtCompanyName.Text.Trim)
            cmd.Parameters.AddWithValue("@SNo", prospect)
            cmd.ExecuteNonQuery()
            'deleting old rows
            cmd.CommandText = "delete from ProspectContacts where ProsID=" & prospect
            cmd.ExecuteNonQuery()
            For Each row As DataGridViewRow In GridContactPerson.Rows
                If row.Index = GridContactPerson.Rows.Count - 1 Then Exit For

                If (row.Cells("Email").Value Like "*@*.*") = False Then
                    row.ErrorText = "Please Enter a Valid Email"
                    Exit Sub
                Else
                    row.ErrorText = ""
                End If

                cmd.Parameters.Clear()
                cmd.CommandText = "insert into ProspectContacts (ProsID,ContactName,Position,Email,Tele) values (@ProsID,@ContactName,@Position,@Email,@Tele)"
                cmd.Parameters.AddWithValue("@ProsID", prospect)
                cmd.Parameters.AddWithValue("@ContactName", row.Cells("ContactPerson").Value)
                cmd.Parameters.AddWithValue("@Position", row.Cells("Position").Value)
                cmd.Parameters.AddWithValue("@Email", row.Cells("Email").Value)
                cmd.Parameters.AddWithValue("@Tele", row.Cells("MobileNo").Value)

                cmd.ExecuteNonQuery()
            Next
            trans.Commit()
            MsgBox("Saved Successfuly!")
            clear()
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
        End Try
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        save()
    End Sub

    Private Sub frmEditProspect_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        BindProspect(prospect)
        'GridContactPerson.EditMode = DataGridViewEditMode.EditOnKeystrokeOrF2
    End Sub
End Class